﻿using System;
using System.Data;
using System.Data.SQLite;
using HighDiveEngine.Resources;

namespace HighDiveEngine.IoEngines
{
    public class DbEngine : IDisposable
    {
        private static readonly log4net.ILog Log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        #region PropertiesAndMemberdata
        private SQLiteConnection _sqlCon;
        private string _dbLocation;

        public string DbLocation
        {
            get { return _dbLocation; }
            set
            {
                if (_sqlCon != null)
                    _sqlCon.Dispose();
                _dbLocation = value;
                if (System.IO.File.Exists(_dbLocation))
                {
                    _sqlCon = new SQLiteConnection("Data Source = " + _dbLocation + ";Version=3;New=False;Compress=True;");
                }
                else
                {
                    _sqlCon = new SQLiteConnection("Data Source = " + _dbLocation + ";Version=3;New=True;Compress=True;");
                }
                if (Log.IsInfoEnabled) { Log.Info("Database file set to " + value + " setting up tables"); }
                _setupTablesInDb();
            }
        } 
        #endregion

        #region TableSetup
        private void _setupTablesInDb()
        {
            //Persons-table
            SqlCommand("CREATE TABLE IF NOT EXISTS Persons (" +
                                 "Id INTEGER PRIMARY KEY ASC, " +
                                 "IdNumber TEXT NOT NULL UNIQUE, " +
                                 "FirstName TEXT NOT NULL, " +
                                 "LastName TEXT NOT NULL, " +
                                 "Club TEXT DEFAULT NULL," +
                                 "Email TEXT DEFAULT NULL, " +
                                 "Address TEXT DEFAULT NULL, " +
                                 "Gender TEXT NOT NULL" +
                                 ");");

            if (Log.IsInfoEnabled) { Log.Info("Finished setting up Persons-table"); }

            //Judges-table
            SqlCommand("CREATE TABLE IF NOT EXISTS Judges (" +
                                 "Id INTEGER PRIMARY KEY ASC, " +
                                 "IdString TEXT DEFAULT NULL, " +
                                 "JudgeId TEXT NOT NULL," +
                                 "Contest INTEGER NOT NULL, " +
                                 "JudgeNumber INTEGER NOT NULL, " +
                                 "FOREIGN KEY (JudgeId) REFERENCES Persons(Id), " +
                                 "UNIQUE (JudgeId, Contest), " +
                                 "UNIQUE (IdString, Contest)" +
                                 ");");

            if (Log.IsInfoEnabled) { Log.Info("Finished setting up Judges-table"); }

            //Contests-table
            SqlCommand("CREATE TABLE IF NOT EXISTS Contests (" +
                                 "Id INTEGER PRIMARY KEY ASC, " +
                                 "Name TEXT NOT NULL, " +
                                 "Date TEXT NOT NULL, " +
                                 "Club TEXT NOT NULL," +
                                 "City TEXT DEFAULT NULL, " +
                                 "Country TEXT DEFAULT NULL, " +
                                 "NumJudges INTEGER NOT NULL, " +
                                 "Status TEXT NOT NULL, " +
                                 "UNIQUE(Name, Date, Club)" +
                                 ");");

            if (Log.IsInfoEnabled) { Log.Info("Finished setting up Contests-table"); }

            //Dives-table
            SqlCommand("CREATE TABLE IF NOT EXISTS Dives (" +
                                 "Id INTEGER PRIMARY KEY ASC, " +
                                 "Diver INTEGER NOT NULL, " +
                                 "Contest INTEGER NOT NULL, " +
                                 "DiveCode TEXT NOT NULL, " +
                                 "Difficulty REAL NOT NULL, " +
                                 "DiveNumber INTEGER NOT NULL, " +
                                 "DiveHeight INTEGER NOT NULL, " +
                                 "QueueOrder INTEGER NOT NULL, " +
                                 "Date TEXT DEFAULT NULL, " +
                                 "FOREIGN KEY (Diver) REFERENCES Divers(Id), " +
                                 "FOREIGN KEY (Contest) REFERENCES Contests(Id), " +
                                 "UNIQUE (Diver, DiveNumber, Contest), " +
                                 "UNIQUE (QueueOrder,Contest)" +
                                 ");");

            if (Log.IsInfoEnabled) { Log.Info("Finished setting up Dives-table"); }

            //DiveScores-table
            SqlCommand("CREATE TABLE IF NOT EXISTS DiveScores(" +
                                 "Id INTEGER PRIMARY KEY ASC, " +
                                 "Dive INTEGER NOT NULL, " +
                                 "Judge INTEGER NOT NULL, " +
                                 "Score REAL NOT NULL, " +
                                 "FOREIGN KEY (Dive) REFERENCES Dives(Id), " +
                                 "FOREIGN KEY (Judge) REFERENCES Judges(Id), " +
                                 "UNIQUE (Dive,Judge)" +
                                 ");");

            if (Log.IsInfoEnabled) { Log.Info("Finished setting up DiveScores-table"); }

            //Divers-table
            SqlCommand("CREATE TABLE IF NOT EXISTS Divers (" +
                                 "Id INTEGER PRIMARY KEY ASC, " +
                                 "Diver INTEGER NOT NULL, " +
                                 "Contest INTEGER NOT NULL, " +
                                 "StartNumber INTEGER NOT NULL, " +
                                 "FOREIGN KEY (Diver) REFERENCES Persons(Id), " +
                                 "FOREIGN KEY (Contest) REFERENCES Contest(Id), " +
                                 "UNIQUE (Contest,StartNumber)" +
                                 ");");

            if (Log.IsInfoEnabled) { Log.Info("Finished setting up Divers-table"); }
        } 
        #endregion

        #region BasicDbInteraction
        /// <summary>
        /// Function to perform SQL-commands without return values on the database
        /// </summary>
        /// <param name="cmdText">String with the SQL-command to run</param>
        /// <example>SqlCommand("INSERT INTO Contests VALUES (...)")</example>
        public void SqlCommand(string cmdText)
        {
            if (Log.IsDebugEnabled) { Log.Debug("DbHandler.SqlCommand(" + cmdText + ")"); }
            _sqlCon.Open();
            var sqlCmd = _sqlCon.CreateCommand();
            sqlCmd.CommandText = cmdText;
            try
            {
                sqlCmd.ExecuteNonQuery();
            }
            catch (SQLiteException e)
            {
                if (Log.IsErrorEnabled) { Log.Error("Exception from SqlCommand(" + cmdText + ") " + e); }
                throw new DbEngineException("Exception from SqlCommand(" + cmdText + ")");
            }
            finally
            {
                _sqlCon.Close();
            }
        }

        /// <summary>
        /// Function to perform querys on the dataase with SELECT
        /// </summary>
        /// <param name="cmdText">String with the SQL-query to run</param>
        /// <returns>A DataTable containing the result of the query</returns>
        /// <example>SqlQuery("SELECT * FROM Dives")</example>
        public DataTable SqlQuery(string cmdText)
        {
            if (Log.IsDebugEnabled) { Log.Debug("DbHandler.SqlQuery(" + cmdText + ")"); }
            var dt = new DataTable();
            _sqlCon.Open();
            try
            {
                var db = new SQLiteDataAdapter(cmdText, _sqlCon);
                var ds = new DataSet();
                db.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (SQLiteException e)
            {
                if (Log.IsErrorEnabled) { Log.Error("Exception from SqlQuery(" + cmdText + ") " + e); }
                throw new DbEngineException("Exception from SqlQuery(" + cmdText + ") ");
            }
            finally
            {
                _sqlCon.Close();
            }
            return dt;
        } 
        #endregion

        #region IDisposable
        public void Dispose()
        {
            _sqlCon.Dispose();
        }
        #endregion
    }
}
